package com.huike.pmps.dao;

import com.huike.pmps.model.dto.cockpit.admin.*;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 作者：谌贵斌(黑桃K)
 * 日期: 2888-08-08
 * 老师QQ: 272488352
 * 官方QQ交流群: 377748272
 * 刀客程序员官网：http://www.daoke360.com
 * 刀客程序员淘宝旗舰店：https://daoke360.taobao.com/
 */
public interface CockpitAdminDao {

    @Select({
            "<script>",
            "SELECT region_name,collection_amount,payment_amount,salary,																	",
            "(IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                           ",
            "ROUND((IFNULL(collection_amount,0)-IFNULL(payment_amount,0)-IFNULL(salary,0))/IFNULL(collection_amount,0),2) gross_profit_rate ",
            "FROM                                                                                                                           ",
            "(                                                                                                                              ",
            "	SELECT pr.region region_name ,pc.collection_amount,ep.payment_amount,pct.salary                                             ",
            "	FROM p_region pr                                                                                                            ",
            "	LEFT JOIN                                                                                                                   ",
            "	(                                                                                                                           ",
            "		SELECT                                                                                                                  ",
            "		region_name,                                                                                                            ",
            "		SUM(collection_amount)  collection_amount                                                                               ",
            "		FROM  `p_payment_collection`                                                                                            ",
            "		<where>                                                                                                                 ",
            "			<if test='year!=null'>                                                                                              ",
            "				and  year=#{year}                                                                                               ",
            "			</if>                                                                                                               ",
            "			<if test='month!=null'>                                                                                             ",
            "				and  month_of_year=#{month}                                                                                     ",
            "			</if>                                                                                                               ",
            "		</where>                                                                                                                ",
            "		GROUP BY region_name                                                                                                    ",
            "	) pc ON pr.region=pc.region_name                                                                                            ",
            "	LEFT JOIN(                                                                                                                  ",
            "		SELECT                                                                                                                  ",
            "		region_name,                                                                                                            ",
            "		SUM(payment_amount)payment_amount                                                                                       ",
            "		FROM  `p_expenses`                                                                                                      ",
            "		<where>                                                                                                                 ",
            "			<if test='year!=null'>                                                                                              ",
            "				and  year=#{year}                                                                                               ",
            "			</if>                                                                                                               ",
            "			<if test='month!=null'>                                                                                             ",
            "				and  month_of_year=#{month}                                                                                     ",
            "			</if>                                                                                                               ",
            "		</where>                                                                                                                ",
            "		GROUP BY region_name                                                                                                    ",
            "	)ep ON pr.region=ep.region_name                                                                                             ",
            "	LEFT JOIN                                                                                                                   ",
            "	(                                                                                                                           ",
            "		SELECT                                                                                                                  ",
            "		region_name,                                                                                                            ",
            "		SUM(salary)salary                                                                                                       ",
            "		FROM `p_personnel_cost`                                                                                                 ",
            "		<where>                                                                                                                 ",
            "			<if test='year!=null'>                                                                                              ",
            "				and  year=#{year}                                                                                               ",
            "			</if>                                                                                                               ",
            "			<if test='month!=null'>                                                                                             ",
            "				and  month_of_year=#{month}                                                                                     ",
            "			</if>                                                                                                               ",
            "		</where>                                                                                                                ",
            "		GROUP BY region_name                                                                                                    ",
            "	)pct ON pr.region=pct.region_name                                                                                           ",
            "	WHERE pr.parent_id=0                                                                                                        ",
            ")temp                                                                                                                          ",
            "</script>"
    })
    List<OperationGrossProfitAdminDTO> getGrossProfitBy(Integer year, Integer month);


    @Select({
            "<script>",
            "SELECT 																						",
            "region_name,                                                                                   ",
            "contract_amount,                                                                               ",
            "collection_amount,		                                                                        ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                   ",
            "payment_amount                                                                                 ",
            "FROM                                                                                           ",
            "(                                                                                              ",
            "	SELECT pr.region region_name ,ns.contract_amount,pc.collection_amount,ep.payment_amount     ",
            "	FROM p_region pr                                                                            ",
            "	LEFT JOIN                                                                                   ",
            "	(                                                                                           ",
            "		SELECT                                                                                  ",
            "		region_name,                                                                            ",
            "		SUM(contract_amount)  contract_amount                                                   ",
            "		FROM `p_new_sign`                                                                       ",
            "		<where>                                                                                 ",
            "			<if test='year!=null'>                                                              ",
            "				and  year=#{year}                                                               ",
            "			</if>                                                                               ",
            "			<if test='month!=null'>                                                             ",
            "				and  month_of_year=#{month}                                                     ",
            "			</if>                                                                               ",
            "		</where>                                                                                ",
            "		GROUP BY region_name                                                                    ",
            "	) ns ON pr.region=ns.region_name                                                            ",
            "	LEFT JOIN(                                                                                  ",
            "		SELECT                                                                                  ",
            "		region_name,                                                                            ",
            "		SUM(collection_amount) collection_amount                                                ",
            "		FROM p_payment_collection                                                               ",
            "		<where>                                                                                 ",
            "			<if test='year!=null'>                                                              ",
            "				and  year=#{year}                                                               ",
            "			</if>                                                                               ",
            "			<if test='month!=null'>                                                             ",
            "				and  month_of_year=#{month}                                                     ",
            "			</if>                                                                               ",
            "		</where>                                                                                ",
            "		GROUP BY region_name                                                                    ",
            "	)pc ON pr.region=pc.region_name                                                             ",
            "	LEFT JOIN                                                                                   ",
            "	(                                                                                           ",
            "		SELECT                                                                                  ",
            "		region_name,                                                                            ",
            "		SUM(payment_amount) payment_amount                                                      ",
            "		FROM `p_expenses`                                                                       ",
            "		<where>                                                                                 ",
            "			<if test='year!=null'>                                                              ",
            "				and  year=#{year}                                                               ",
            "			</if>                                                                               ",
            "			<if test='month!=null'>                                                             ",
            "				and  month_of_year=#{month}                                                     ",
            "			</if>                                                                               ",
            "		</where>                                                                                ",
            "		GROUP BY region_name                                                                    ",
            "	)ep ON pr.region=ep.region_name                                                             ",
            "	WHERE pr.parent_id=0                                                                        ",
            ")temp                                                                                          ",
            "</script>"
    })
    List<OperationFinanceAdminDTO> getFinance(Integer year, Integer month);




    @Select({
            "<script>",
            "  SELECT rd.region_name, rd.product_type_name,bo.contract_amount,bo.contract_count 	",
            "  FROM (                                                                               ",
            "  	SELECT region region_name,product_type_name                                         ",
            "  	FROM p_region pr,                                                                   ",
            "  	(SELECT NAME product_type_name FROM p_dictionary WHERE TYPE=1) pd                   ",
            "  	WHERE pr.parent_id=0                                                                ",
            "  )rd                                                                                  ",
            "  LEFT JOIN                                                                            ",
            "  (                                                                                    ",
            "  	SELECT region_name,product_type_name,                                               ",
            "  	SUM(contract_amount) contract_amount,                                               ",
            "  	COUNT(id)contract_count                                                            ",
            "  	FROM p_business_opportunity                                                         ",
            "  	<where>                                                                             ",
            "  		<if test='year!=null'>                                                          ",
            "  			and  year=#{year}                                                           ",
            "  		</if>                                                                           ",
            "  		<if test='month!=null'>                                                         ",
            "  			and  month_of_year=#{month}                                                 ",
            "  		</if>                                                                           ",
            "  	</where>                                                                            ",
            "  	GROUP BY region_name,product_type_name                                              ",
            "  ) bo                                                                                 ",
            "   ON rd.region_name=bo.region_name AND rd.product_type_name=bo.product_type_name      ",
            "   ORDER BY   rd.region_name                                                           ",
            "</script>"
    })
    List<BusinessOpportunityLineAdminDTO> getBusinessOpportunities(Integer year, Integer month);


    @Select({
            "<script>",
            "	SELECT *,(stage_30+stage_40+stage_50+stage_60+stage_70+stage_80+stage_90)total_amount FROM 				",
            "	(                                                                                                       ",
            "		SELECT region_name,                                                                                 ",
            "		(CASE a.stage        WHEN '30%' THEN a.contract_amount        ELSE 0    END) 'stage_30',            ",
            "		(CASE a.stage        WHEN '40%' THEN a.contract_amount        ELSE 0    END) 'stage_40',            ",
            "		(CASE a.stage        WHEN '50%' THEN a.contract_amount        ELSE 0    END) 'stage_50',            ",
            "		(CASE a.stage        WHEN '60%' THEN a.contract_amount        ELSE 0    END) 'stage_60',            ",
            "		(CASE a.stage        WHEN '70%' THEN a.contract_amount        ELSE 0    END) 'stage_70',            ",
            "		(CASE a.stage        WHEN '80%' THEN a.contract_amount        ELSE 0    END) 'stage_80',            ",
            "		(CASE a.stage        WHEN '90%' THEN a.contract_amount        ELSE 0    END) 'stage_90'             ",
            "		FROM                                                                                                ",
            "		(                                                                                                   ",
            "			SELECT pr.region region_name,tmp.stage,tmp.contract_amount                                      ",
            "				FROM p_region pr                                                                            ",
            "				LEFT JOIN                                                                                   ",
            "				(                                                                                           ",
            "				SELECT region_name,CONCAT(ROUND(stage*100,0),'%')stage ,SUM(contract_amount)contract_amount ",
            "				FROM(                                                                                       ",
            "						SELECT                                                                              ",
            "						region_name,                                                                        ",
            "						CASE                                                                                ",
            "						WHEN stage>=0.9 THEN 0.9                                                            ",
            "						WHEN stage>=0.8 THEN 0.8                                                            ",
            "						WHEN stage>=0.7 THEN 0.7                                                            ",
            "						WHEN stage>=0.6 THEN 0.6                                                            ",
            "						WHEN stage>=0.5 THEN 0.5                                                            ",
            "						WHEN stage>=0.4 THEN 0.4                                                            ",
            "						WHEN stage>=0.3 THEN 0.3                                                            ",
            "						END stage,                                                                          ",
            "						contract_amount                                                                     ",
            "						FROM p_business_opportunity                                                         ",
            "						<where>                                                                             ",
            "							stage >=0.3                                                                     ",
            "							<if test='year!=null'>                                                          ",
            "								and  year=#{year}                                                           ",
            "							</if>                                                                           ",
            "							<if test='month!=null'>                                                         ",
            "								and  month_of_year=#{month}                                                 ",
            "							</if>                                                                           ",
            "						</where>                                                                            ",
            "				)temp GROUP BY region_name,stage                                                            ",
            "				                                                                                            ",
            "				)tmp                                                                                        ",
            "				ON pr.region=tmp.region_name                                                                ",
            "				WHERE pr.parent_id=0 						                                                ",
            "		)a GROUP BY region_name                                                                             ",
            "	)temp                                                                                                   ",
            "</script>"
    })
    List<BusinessOpportunity30AdminDTO> getBusinessOpportunitiesStage30(Integer year, Integer month);


    @Select({
            "<script>",
            " SELECT 																															",
            " '全部' region_name,																					                            ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='未启动'  )a,                                                       ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='沟通中'  )b,                                                       ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='沟通100%')c,                                                       ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='拍摄中'  )d,                                                       ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='拍摄100%')e,                                                       ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='制作中'  )f,                                                       ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='制作100%')g,                                                       ",
            " COUNT(id) total_count                                                                                                             ",
            " FROM p_po_course WHERE course_schedule_name &lt;&gt; '已完结'                                                                     ",
            "                                                                                                                                   ",
            " UNION ALL                                                                                                                         ",
            "                                                                                                                                   ",
            " SELECT pc.region_name ,a.a,b.b,c.c,d.d,e.e,f.f,g.g,h.total_count                                                                  ",
            " FROM p_po_course pc                                                                                                               ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) a FROM p_po_course WHERE course_schedule_name='未启动'   GROUP BY region_name                         ",
            " ) a ON pc. region_name=a.region_name                                                                                              ",
            "                                                                                                                                   ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) b FROM p_po_course WHERE course_schedule_name='沟通中'   GROUP BY region_name                         ",
            " ) b ON pc. region_name=b.region_name                                                                                              ",
            "                                                                                                                                   ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) c FROM p_po_course WHERE course_schedule_name='沟通100%'   GROUP BY region_name                       ",
            " ) c ON pc. region_name=c.region_name                                                                                              ",
            "                                                                                                                                   ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) d FROM p_po_course WHERE course_schedule_name='拍摄中'    GROUP BY region_name                        ",
            " ) d ON pc. region_name=d.region_name                                                                                              ",
            "                                                                                                                                   ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) e FROM p_po_course WHERE course_schedule_name='拍摄100%'   GROUP BY region_name                       ",
            " ) e ON pc. region_name=e.region_name                                                                                              ",
            "                                                                                                                                   ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) f FROM p_po_course WHERE course_schedule_name='制作中'    GROUP BY region_name                        ",
            " ) f ON pc. region_name=f.region_name                                                                                              ",
            "                                                                                                                                   ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) g FROM p_po_course WHERE course_schedule_name='制作100%'   GROUP BY region_name                       ",
            " ) g ON pc. region_name=g.region_name                                                                                              ",
            " LEFT JOIN(                                                                                                                        ",
            " SELECT region_name,COUNT(*) total_count FROM p_po_course WHERE course_schedule_name &lt;&gt; '已完结'   GROUP BY region_name      ",
            " )h ON pc. region_name=h.region_name                                                                                               ",
            "  GROUP BY region_name                                                                                                             ",
            "</script>"
    })
    List<OnLineStockAdminDTO> getOnLineCourseStock();


    @Select("select month_of_year month,round(sum(capacity)/60,2) capacity  from p_course_capacity where year=year(now()) ")
    List<CourseCapacityLineAdminDTO> getCourseCapacity();



    @Select({
            "<script>",
            "	SELECT  																									",
            "	pd.product_type_name,                                                                                       ",
            "	ns.contract_number,                                                                                         ",
            "	ns.contract_amount,                                                                                         ",
            "	pc.collection_amount,                                                                                       ",
            "	(IFNULL(ns.contract_amount,0)-IFNULL(pc.collection_amount,0))collection_un_amount                           ",
            "	FROM (SELECT  NAME product_type_name FROM p_dictionary WHERE TYPE=1) pd                                     ",
            "	LEFT JOIN                                                                                                   ",
            "	(                                                                                                           ",
            "		SELECT product_type_name, COUNT(id)contract_number,SUM(contract_amount)contract_amount                  ",
            "		FROM p_new_sign                                                                                         ",
            "		<where>                                                                                                 ",
            "			<if test='year!=null'>                                                                              ",
            "				and  year=#{year}                                                                               ",
            "			</if>                                                                                               ",
            "			<if test='month!=null'>                                                                             ",
            "				and  month_of_year=#{month}                                                                     ",
            "			</if>                                                                                               ",
            "		</where>                                                                                                ",
            "		GROUP BY product_type_name                                                                              ",
            "	) ns  ON pd.product_type_name=ns.product_type_name                                                          ",
            "	LEFT JOIN                                                                                                   ",
            "	(                                                                                                           ",
            "		SELECT                                                                                                  ",
            "		product_type_name,                                                                                      ",
            "		SUM(collection_amount) collection_amount                                                                ",
            "		FROM p_payment_collection                                                                               ",
            "		<where>                                                                                                 ",
            "			<if test='year!=null'>                                                                              ",
            "				and  year=#{year}                                                                               ",
            "			</if>                                                                                               ",
            "			<if test='month!=null'>                                                                             ",
            "				and  month_of_year=#{month}                                                                     ",
            "			</if>                                                                                               ",
            "		</where>                                                                                                ",
            "		GROUP BY product_type_name                                                                              ",
            "	)pc  ON pd.product_type_name=pc.product_type_name                                                           ",
            "</script>"
    })
    List<OnLineCourseOperationAdminDTO>getOnLineCourseOperation(Integer year, Integer month);
}
